// --------------------------------------------------------------------------------------------------------------------
// <copyright file="SqlAzureAdapter.cs" company="">
//   
// </copyright>
// --------------------------------------------------------------------------------------------------------------------
namespace ExecutionTool.CopyDatabase
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;

    using ExecutionTool.Common;
    using ExecutionTool.Profile;

    using Microsoft.Practices.EnterpriseLibrary.Logging;
    using Microsoft.Practices.ServiceLocation;

    public class SqlAzureAdapter : ISqlAzureAdapter
    {
        #region Constants

        private const string ConnectionStringTemplate = "Server=tcp:{0}.database.windows.net,1433;Initial Catalog={3};User ID={1};Password={2};";

        private const string CopyDatabaseTemplate = "CREATE DATABASE [{1}] AS COPY OF [{0}]";

        private const string DeleteDatabaseTemplate = "drop DATABASE [{0}]";

        private const string EditionCapacityInfoTemplate = @"declare @edition nvarchar(50)
declare @maxSizeInBytes bigint
declare @actualSizeInMb int
SELECT @edition        = cast(DATABASEPROPERTYEX('{0}', 'Edition') as nvarchar(50))
SELECT @maxSizeInBytes = cast(DATABASEPROPERTYEX('{0}', 'MaxSizeInBytes') as bigint)
select @actualSizeInMb     = sum(reserved_page_count) * 8.0 / 1024 from sys.dm_db_partition_stats
select @edition as Edition, @maxSizeInBytes as MaxSizeInBytes, @actualSizeInMb as ActualSizeInMb";

        private const string MasterDatabaseName = "master";

        private const string RenameDatabaseTemplate = @"ALTER DATABASE [{0}] MODIFY NAME = [{1}]";

        ////WAITFOR DELAY '00:00:30'
        private const string SystemDatabaseCopyViewTemplate =
            @"select database_id, percent_complete, error_code, error_desc from sys.dm_database_copies where database_id = {0}";

        private const string SystemDatabaseViewAllTemplate = @"select database_id, name, state_desc from sys.databases";

        private const string SystemDatabaseViewTemplate = @"select database_id, name, state_desc from sys.databases where name like '{0}'";

        #endregion

        #region Fields

        private readonly LogWriter logger;

        #endregion

        #region Constructors and Destructors

        public SqlAzureAdapter(LogWriter logger = null)
        {
            this.logger = logger ?? ServiceLocator.Current.GetInstance<LogWriter>();
        }

        #endregion

        #region Public Methods and Operators

        public bool DatabaseExists(SqlAzureLocation sourceSqlAzureLocation, string databaseName)
        {
            this.logger.LogVerbose(
                string.Format("Checking whether database [{0}] on [{1}] exists...", databaseName, sourceSqlAzureLocation.ServerName));

            var sqldb = string.Format(SystemDatabaseViewTemplate, databaseName);
            var exists = ExecuteReaderCommand(
                sourceSqlAzureLocation, 
                MasterDatabaseName, 
                sqldb, 
                reader =>
                    {
                        if (reader.HasRows)
                        {
                            return true;
                        }

                        return false;
                    });

            this.logger.LogVerbose(
                string.Format(
                    "Checked that database [{0}] on [{1}] {2}...", 
                    databaseName, 
                    sourceSqlAzureLocation.ServerName, 
                    exists ? "already exists" : "doesn't exist"));

            return exists;
        }

        public void DeleteDatabase(SqlAzureLocation sourceSqlAzureLocation, string targetDatabaseName)
        {
            this.logger.LogVerbose(string.Format("Deleting database [{0}] on [{1}]...", targetDatabaseName, sourceSqlAzureLocation.ServerName));

            var sql = string.Format(DeleteDatabaseTemplate, targetDatabaseName);
            ExecuteNonQueryCommand(
                sourceSqlAzureLocation, 
                MasterDatabaseName, 
                sql, 
                ex =>
                    {
                        this.logger.LogWarning(
                            string.Format(
                                "Deleting database [{0}] on [{1}] failed. --> {2}", targetDatabaseName, sourceSqlAzureLocation.ServerName, ex), 
                            ErrorCodes.DatabaseDeleteError);
                        return 0;
                    });

            this.logger.LogVerbose(string.Format("Deleted database [{0}] on [{1}].", targetDatabaseName, sourceSqlAzureLocation.ServerName));
        }

        public double GetCopyDatabaseProgress(SqlAzureLocation sourceSqlAzureLocation, string targetDatabaseName)
        {
            this.logger.LogVerbose(
                string.Format("Getting copy progress of database [{0}] on [{1}]...", targetDatabaseName, sourceSqlAzureLocation.ServerName));

            var sqldb = string.Format(SystemDatabaseViewTemplate, targetDatabaseName);
            var resultBasedOnDbView = ExecuteReaderCommand(
                sourceSqlAzureLocation, 
                MasterDatabaseName, 
                sqldb, 
                reader =>
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();

                            var record = new DatabaseViewRecord { Id = (int)reader[0], Name = (string)reader[1], State = reader[2].ToString(), };

                            return record.State == "ONLINE"
                                       ? new KeyValuePair<int, double>(record.Id, 100)
                                       : new KeyValuePair<int, double>(record.Id, -1);
                        }

                        return new KeyValuePair<int, double>(0, 0);
                    });

            if (resultBasedOnDbView.Value >= 0)
            {
                this.logger.LogVerbose(
                    string.Format(
                        "Got copy progress [{2}] of database [{0}] on [{1}].", 
                        targetDatabaseName, 
                        sourceSqlAzureLocation.ServerName, 
                        resultBasedOnDbView.Value));

                return resultBasedOnDbView.Value;
            }

            var sqldbcopy = string.Format(SystemDatabaseCopyViewTemplate, resultBasedOnDbView.Key);
            var result = ExecuteReaderCommand(
                sourceSqlAzureLocation, 
                MasterDatabaseName, 
                sqldbcopy, 
                reader =>
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();

                            var record = new DatabaseCopyViewRecord
                                {
                                    Id = (int)reader[0], 
                                    Percent = reader[1] == DBNull.Value ? 0 : (float)reader[1], 
                                    ErrorCode = reader[2] == DBNull.Value ? (int?)null : (int)reader[2], 
                                    ErrorDescription = reader[3] == DBNull.Value ? null : (string)reader[3], 
                                };

                            if (record.ErrorCode != null)
                            {
                                throw new CopyDatabaseException(record.ErrorDescription, ErrorCodes.DatabaseCopyError, record.ErrorCode.Value);
                            }

                            return record.Percent;
                        }

                        return 0;
                    });

            this.logger.LogVerbose(
                string.Format("Got copy progress [{2}] of database [{0}] on [{1}].", targetDatabaseName, sourceSqlAzureLocation.ServerName, result));

            return result;
        }

        public SqlAzureDatabaseInfo GetDatabaseInfo(SqlAzureLocation sourceSqlAzureLocation, string databaseName = null)
        {
            var targetDatabaseName = string.IsNullOrWhiteSpace(databaseName) ? sourceSqlAzureLocation.DatabaseName : databaseName.Trim();

            this.logger.LogVerbose(
                string.Format(
                    "Getting edition, max size and actual size of database [{0}] on [{1}]...", targetDatabaseName, sourceSqlAzureLocation.ServerName));

            var sql = string.Format(EditionCapacityInfoTemplate, targetDatabaseName);
            return ExecuteReaderCommand(
                sourceSqlAzureLocation, 
                targetDatabaseName, 
                sql, 
                reader =>
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            var editionText = (string)reader[0];
                            var maxSizeInBytes = (long)reader[1];
                            var actualSizeInMb = Convert.ToDouble(reader[2]);

                            this.logger.LogVerbose(
                                string.Format(
                                    "Got edition [{0}], max size [{1}B] and actual size [{2}MB] of database [{3}] on [{4}].", 
                                    editionText, 
                                    maxSizeInBytes, 
                                    actualSizeInMb, 
                                    targetDatabaseName, 
                                    sourceSqlAzureLocation.ServerName));

                            return new SqlAzureDatabaseInfo(editionText, maxSizeInBytes, actualSizeInMb);
                        }

                        throw new StepFaultException(
                            "Failed to retrieve SQL Azure database information.", StepType.CopyDatabase, ErrorCodes.DatabaseGetInformationError);
                    });
        }

        public IList<string> GetExistingDatabaseNames(SqlAzureLocation sourceSqlAzureLocation)
        {
            this.logger.LogVerbose(string.Format("Getting names of the existing databases [{0}]...", sourceSqlAzureLocation.ServerName));

            var resultBasedOnDbView = ExecuteReaderCommand(
                sourceSqlAzureLocation, 
                MasterDatabaseName, 
                SystemDatabaseViewAllTemplate, 
                reader =>
                    {
                        var list = new List<DatabaseViewRecord>();
                        while (reader.Read())
                        {
                            var record = new DatabaseViewRecord { Id = (int)reader[0], Name = (string)reader[1], State = reader[2].ToString(), };
                            list.Add(record);
                        }

                        return list;
                    });

            this.logger.LogVerbose(string.Format("Got names of the existing databases [{0}].", sourceSqlAzureLocation.ServerName));

            return resultBasedOnDbView.Where(r => !r.Name.Equals(MasterDatabaseName)).Select(r => r.Name).ToList();
        }

        public void RenameDatabase(SqlAzureLocation sourceSqlAzureLocation, string tempDatabaseName, string databaseName)
        {
            this.logger.LogVerbose(
                string.Format("Renaming database from [{0}] to [{1}] on [{2}]...", tempDatabaseName, databaseName, sourceSqlAzureLocation.ServerName));

            var sql = string.Format(RenameDatabaseTemplate, tempDatabaseName, databaseName);
            ExecuteNonQueryCommand(
                sourceSqlAzureLocation, 
                MasterDatabaseName, 
                sql, 
                ex =>
                    {
                        this.logger.LogWarning(
                            string.Format(
                                "Renaming database from [{0}] to [{1}] on [{2}] failed. --> {3}", 
                                tempDatabaseName, 
                                databaseName, 
                                sourceSqlAzureLocation.ServerName, 
                                ex), 
                            ErrorCodes.DatabaseRenameError);
                        return 0;
                    });

            this.logger.LogVerbose(
                string.Format("Renamed database from [{0}] to [{1}] on [{2}]...", tempDatabaseName, databaseName, sourceSqlAzureLocation.ServerName));
        }

        public void StartCopyDatabase(SqlAzureLocation sourceSqlAzureLocation, string targetDatabaseName)
        {
            this.logger.LogVerbose(
                string.Format("Starting to copy database [{0}] on [{1}]...", targetDatabaseName, sourceSqlAzureLocation.ServerName));

            var sql = string.Format(CopyDatabaseTemplate, sourceSqlAzureLocation.DatabaseName, targetDatabaseName);
            ExecuteNonQueryCommand(sourceSqlAzureLocation, MasterDatabaseName, sql);

            this.logger.LogVerbose(string.Format("Started to copy database [{0}] on [{1}].", targetDatabaseName, sourceSqlAzureLocation.ServerName));
        }

        public void TestConnection(SqlAzureLocation sourceSqlAzureLocation)
        {
            this.logger.LogVerbose(string.Format("Testing SQL Azure location for existing databases [{0}]...", sourceSqlAzureLocation.ServerName));

            this.GetDatabaseInfo(sourceSqlAzureLocation);

            this.logger.LogVerbose(string.Format("Tested SQL Azure location for existing databases [{0}].", sourceSqlAzureLocation.ServerName));
        }

        #endregion

        #region Methods

        private static T ExecuteHelper<T>(
            SqlAzureLocation sourceSqlAzureLocation, 
            string databaseName, 
            string commandText, 
            Func<SqlCommand, T> func, 
            Func<Exception, T> exceptionHandler = null)
        {
            var connectionString = string.Format(
                ConnectionStringTemplate, 
                sourceSqlAzureLocation.ServerNamePrefix, 
                sourceSqlAzureLocation.UserName, 
                sourceSqlAzureLocation.Password, 
                databaseName);

            try
            {
                using (var connection = new SqlConnection(connectionString))
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = commandText;

                        connection.Open();

                        return func(command);
                    }
                }
            }
            catch (Exception exception)
            {
                if (exceptionHandler == null)
                {
                    throw;
                }

                return exceptionHandler(exception);
            }
        }

        private static void ExecuteNonQueryCommand(
            SqlAzureLocation sourceSqlAzureLocation, string databaseName, string commandText, Func<Exception, int> exceptionHandler = null)
        {
            ExecuteHelper(sourceSqlAzureLocation, databaseName, commandText, command => command.ExecuteNonQuery(), exceptionHandler);
        }

        private static T ExecuteReaderCommand<T>(
            SqlAzureLocation sourceSqlAzureLocation, 
            string databaseName, 
            string commandText, 
            Func<SqlDataReader, T> dataProcessor, 
            Func<Exception, T> exceptionHandler = null)
        {
            return ExecuteHelper(
                sourceSqlAzureLocation, 
                databaseName, 
                commandText, 
                command =>
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            return dataProcessor(reader);
                        }
                    }, 
                exceptionHandler);
        }

        #endregion

        private class DatabaseCopyViewRecord
        {
            #region Public Properties

            public int? ErrorCode { get; set; }

            public string ErrorDescription { get; set; }

            public int Id { get; set; }

            public double Percent { get; set; }

            #endregion
        }

        private class DatabaseViewRecord
        {
            #region Public Properties

            public int Id { get; set; }

            public string Name { get; set; }

            public string State { get; set; }

            #endregion
        }
    }
}
